USE SqlTest
GO

IF OBJECT_ID(N'dbo.AlTreeTable', N'U') IS NOT NULL
	DROP TABLE [dbo].[AlTreeTable]
GO
CREATE TABLE [dbo].[AlTreeTable]
(
	[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[parent_id] [bigint] NULL,
	[name] [varchar](max) NOT NULL
)

CREATE INDEX fk_tree_tree ON AlTreeTable (parent_id);

IF OBJECT_ID(N'dbo.MpTreeTable', N'U') IS NOT NULL
	DROP TABLE [dbo].[MpTreeTable]
GO
CREATE TABLE [dbo].[MpTreeTable]
(
	[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[name] [varchar](max) NULL,
	[path] [varchar](900) NULL,
	[level] int NOT NULL
)
CREATE INDEX mpp_idx ON MpTreeTable (path);

IF OBJECT_ID(N'dbo.NsTreeTable', N'U') IS NOT NULL
	DROP TABLE [dbo].[NsTreeTable]
GO
CREATE TABLE [dbo].[NsTreeTable]
(
	[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,	
	[name] [varchar](max) NULL,
	[Lft] [int] NULL,
	[Rgt] [int] NULL,
	[Level] [int] NULL
)
CREATE INDEX nslrl_idx ON NsTreeTable (lft, rgt, level)

GO
IF EXISTS (SELECT LOGINNAME FROM MASTER.DBO.SYSLOGINS WHERE NAME='sqltest' AND DBNAME='SqlTest')
BEGIN
	DROP USER sqltest
	DROP LOGIN sqltest
END
GO
GO
CREATE LOGIN sqltest WITH PASSWORD='sqltest', DEFAULT_DATABASE=[SqlTest], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER sqltest FOR LOGIN sqltest

GRANT SELECT ON dbo.AlTreeTable TO sqltest
GRANT UPDATE ON dbo.AlTreeTable TO sqltest
GRANT DELETE ON dbo.AlTreeTable TO sqltest
GRANT INSERT ON dbo.AlTreeTable TO sqltest

GRANT SELECT ON dbo.MpTreeTable TO sqltest
GRANT UPDATE ON dbo.MpTreeTable TO sqltest
GRANT DELETE ON dbo.MpTreeTable TO sqltest
GRANT INSERT ON dbo.MpTreeTable TO sqltest

GRANT SELECT ON dbo.NsTreeTable TO sqltest
GRANT UPDATE ON dbo.NsTreeTable TO sqltest
GRANT DELETE ON dbo.NsTreeTable TO sqltest
GRANT INSERT ON dbo.NsTreeTable TO sqltest

EXEC sp_addrolemember N'db_owner', N'sqltest'
EXEC sp_addsrvrolemember N'sqltest', N'sysadmin'

GRANT EXECUTE ON [SET_IDENTITY_GRANT] TO sqltest
GO

